// Created by Windward Studios - no copyright is claimed. This code can be used in
// any manner by anyone for any reason. There is no copyright of any kind on it. You may
// use it in commercial products. You may change it without sharing those changes.
// We ask that you keep the "created by Windward Studios" in a comment at the top.

using System;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using Kailua.net.windward.utils;

namespace net.windward.utils.ado.OleDb
{
	/// <summary>
	/// Wraps a DbCommand object and provides a client independent method of setting parameters. All parameters are
	/// set as ? in the sql commands are are set based on their order. This also has an ExecuteInsert that if given
	/// a sql command to insert a single row, will return the value of the auto primary key for that row.
	/// </summary>
	public class WrOleDbCommand : WrCommand
	{

		private readonly IWrSyntax syntax;

		/// <summary>
		/// Create a DbCommand wrapper.
		/// </summary>
		/// <param name="provider">The provider for this command.</param>
		/// <param name="odbcProvider">The ODBC provider for the connection this command is for. Can be null.</param>
		public WrOleDbCommand(DbProviderFactory provider, string odbcProvider)
		{
			syntax = WrOleDbVendor.CalcSyntax(provider, odbcProvider);
			cmd = new OleDbCommand();
			cmd.CommandType = CommandType.Text;
		}

		/// <summary>
		/// Set the value of a parameter.
		/// </summary>
		/// <param name="ind">The index of this paramater in the command.</param>
		/// <param name="value">The value of this parameter.</param>
		public override void Set(int ind, object value)
		{

			if (value == null)
				value = DBNull.Value;

			OleDbParameter param = new OleDbParameter();
			param.ParameterName = "@p" + ind;
			param.Value = value;

			cmd.Parameters.Add(param);
		}

		///<summary>
		///Used for inserting a row with an auto primary key. Executes an SQL statement against the Connection object of a .NET Framework 
		/// data provider, and returns the value of the primary key. This could easily fail as it is vendor specific and this
		/// just uses the SqlServer syntax.
		///</summary>
		/// <param name="pkName">The name of the primary key being automatically set.</param>
		///<returns>
		///The value of the primary key for the inserted row.
		///</returns>
		///<exception cref="T:System.InvalidOperationException">The connection does not exist -or- The connection is not open. </exception><filterpriority>2</filterpriority>
		public override long ExecuteInsert(string pkName)
		{

			cmd.CommandText += "; select scope_identity()";
			object rtn = cmd.ExecuteScalar();
			return (long)(decimal)rtn;

			/*
			 OleDbCommand cmd = ...;
cmd.CommandText = "INSERT INTO [TABLE**] (Id, UserName) VALUES (**, **) 
SELECT @@IDENTITY AS 'ID'";
OleDbDataReader reader = cmd.ExecuteReader();
If (reader.Read())
{
// get the auto-generated PK from reader[0]
}
			 */
		}

		#region IDbCommand overrides

		///<summary>
		///Gets or sets the text command to run against the data source. This could easily fail as it is vendor specific and this
		/// just uses the SqlServer syntax.
		///</summary>
		///<returns>
		///The text command to execute. The default value is an empty string ("").
		///</returns>
		///<filterpriority>2</filterpriority>
		public override string CommandText
		{
			get
			{
				return cmd.CommandText;
			}
			set
			{
				if (syntax != null)
				{
					cmd.CommandText = syntax.ConvertSelect(value);
					return;
				}
				// handle top N if it exists
				int pos = value.ToLower().LastIndexOf(" top ");
				if (pos == -1)
				{
					cmd.CommandText = value;
					return;
				}
				Trap.trap();
				cmd.CommandText = value.Substring(0, pos).Trim();

				/*
				 3) have a select return the "top N" rows.
Do you mean that you would like to dynamically specify the value of 'N'?
We can do it in this way:
string command = String.Format("SELECT TOP {0} * FROM [TABLE**]", N);
or
string command = String.Format("SET ROWCOUNT {0} SELECT * FROM [TABLE**]", 
N);

4) have a select return the count of how many rows a select will
return.
The @@Rowcount function will be set after any statement that changes or 
returns rows. Therefore, we can do it in this way:

SELECT * FROM [TABLE**] SELECT @@rowcount AS 'RowsReturned'

				 */
			}
		}

		///<summary>
		///Executes the <see cref="P:System.Data.IDbCommand.CommandText"></see> against the 
		/// <see cref="P:System.Data.IDbCommand.Connection"></see> and builds an <see cref="T:System.Data.IDataReader"></see>.
		///</summary>
		///
		///<returns>
		///An <see cref="T:System.Data.IDataReader"></see> object.
		///</returns>
		///<filterpriority>2</filterpriority>
		public override IDataReader ExecuteReader()
		{
			return new WrOleDbDataReader(cmd.ExecuteReader());
		}

		///<summary>
		///Executes the <see cref="P:System.Data.IDbCommand.CommandText"></see> against the 
		/// <see cref="P:System.Data.IDbCommand.Connection"></see>, and builds an <see cref="T:System.Data.IDataReader"></see> 
		/// using one of the <see cref="T:System.Data.CommandBehavior"></see> values.
		///</summary>
		///
		///<returns>
		///An <see cref="T:System.Data.IDataReader"></see> object.
		///</returns>
		///
		///<param name="behavior">One of the <see cref="T:System.Data.CommandBehavior"></see> values. </param>
		/// <filterpriority>2</filterpriority>
		public override IDataReader ExecuteReader(CommandBehavior behavior)
		{
			Trap.trap();
			return new WrOleDbDataReader(cmd.ExecuteReader(behavior));
		}

		#endregion
	}
}
